package com.roc.web.action.merinfo;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;
import com.roc.util.constant.Role;

public class MerinfoChangeQuerySearchCondition  extends BaseSearchCondition{

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private String orgCode ;//所属收单机构
	private String merchantNo;//商户号
	private String nameBusi;//商户简称
	private String name; //工商注册名
	private String cardNo;//证件号
	private String dealCode;//变更类型
	
	private String roleId; //角色 
	private String opId;//操作员id
	
	private String status;//审核结果
	private String groupCd; //受理市场标识
	private String minDate;//审核时间区间左
    private String maxDate;//审核时间区间右
    private String oneOrgCode; //机构号
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		// TODO Auto-generated method stub
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
	        if(Constance.isNotEmpty(merchantNo)){ //商户号
			filedBeanList.add(new WhereHQLFieldBean("m.mid", merchantNo.trim(), "=", false));
		}
      //机构
		if(Constance.isNotEmpty(oneOrgCode)&&!roleId.equals(Role.SYSTEM.getCode())&&!roleId.equals(Role.AGENT.getCode())&&!(roleId.equals(Role.MARKET_DIRECT.getCode())||roleId.equals(Role.MARKET_LEAD.getCode()))){//机构

		    filedBeanList.add(new WhereHQLFieldBean("A.uc_bc_cd_area", oneOrgCode.trim(), "=", false));
		}
		if(Constance.isNotEmpty(nameBusi)){ //商户简称 （模糊）
			filedBeanList.add(new WhereHQLFieldBean("A.NAME_BUSI", nameBusi.trim(), "like", false,"ALL"));
		}
		if(Constance.isNotEmpty(name)){ //工商注册名 （模糊）
			filedBeanList.add(new WhereHQLFieldBean("A.NAME", name.trim(), "like", false,"ALL"));
		}
		if(Constance.isNotEmpty(cardNo)){ //法人身份证号
			filedBeanList.add(new WhereHQLFieldBean("A.CERTIF_NO", cardNo.trim(), "=", false));
		}
		if(Constance.isNotEmpty(groupCd)){ //受理市场标识
			filedBeanList.add(new WhereHQLFieldBean("A.group_cd", groupCd.trim(), "=", false));
		}
		
		if(Constance.isNotEmpty(dealCode)&&!"99".equals(dealCode)){ 
			filedBeanList.add(new WhereHQLFieldBean("TRIM(M.DEAL_CODE)", dealCode.trim(), "=", false));
		}
		if(Constance.isNotEmpty(orgCode)&&!"99".equals(orgCode)){ //机构号
			filedBeanList.add(new WhereHQLFieldBean("A.BANK_BELONG_CD", orgCode.trim(), "=", false));
		}
		if(Constance.isNotEmpty(opId) && Constance.isNotEmpty(roleId) && roleId.equals(Role.AGENT_BUSS.getCode())){//操作员 
	                filedBeanList.add(new WhereHQLFieldBean("A.REC_UPD_OPR", opId.trim(), "=", false));
	        }
		
		if(Constance.isNotEmpty(status)&&!"99".equals(status)){ //机构号
		    filedBeanList.add(new WhereHQLFieldBean("M.STATUS", status.trim(), "=", false));
                }
		
		return filedBeanList;
	
	}

	@Override
	protected String getRowCountHQL() {
	        StringBuffer sb = new StringBuffer();
	        
	        sb.append(" select count(1)                           \n");
	        sb.append("   from T_MERINFO_CHANGE       M,          \n");
	        sb.append("        TBL_MCHT_INF           A,          \n");
	        sb.append("        TBL_INS_INF            TI,         \n");
	        sb.append("        t_operators            T           \n");
	        sb.append("  where m.MID = A.MCHT_CD                  \n");
	        sb.append("    and A.BANK_BELONG_CD = TI.INS_ID_CD    \n");
	        sb.append("    and A.ext1 = to_char(T.ID)             \n");
	        if(Constance.isNotEmpty(minDate)&&Constance.isNotEmpty(maxDate)){ //审核时间
                sb.append("AND M.DT_AUDITOR_TIME >= TO_TIMESTAMP('" + minDate + "','YYYY-MM-DD') " );
                sb.append("AND M.DT_AUDITOR_TIME <= TO_TIMESTAMP('" + maxDate + "','YYYY-MM-DD') + 1 " );
            }
	      //角色为代理商或商服宝会员或父级id为代理商            
 		   if(roleId.equals(Role.AGENT.getCode())||roleId.equals(Role.SFB_AGENT.getCode())){
	    	        sb.append("    and exists(select 1                                                        \n");
	    	        sb.append("           from tbl_mer_agent_relation ma                                      \n");
	    	        sb.append("          where ma.merno = a.mcht_cd                                           \n");
	    	        sb.append("            and ma.agentid =").append(opId).append(" ) \n");
 			}
 		   //登录人角色销售人员                   
 			if(roleId.equals(Role.MARKET_DIRECT.getCode())||roleId.equals(Role.MARKET_LEAD.getCode())){                                               
	    	        sb.append("    and exists (select 1                                                      \n");
	    	        sb.append("                   from t_oper_role orole           \n");
	    	        sb.append("                  where a.uc_bc_cd_area = orole.agentid                             \n");
	    	        sb.append("                    and orole.operid =").append(opId).append("  )   \n");
 			}
	       
		return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
	        StringBuffer sb = new StringBuffer();
            sb.append(" SELECT M.ID,M.DEAL_CODE,M.MID,M.DT_CREATE_TIME,M.DT_AUDITOR_TIME,M.STATUS,");
            sb.append(" A.NAME,A.NAME_BUSI,A.CONTACT_NAME,A.CONTACT_PHONENO,A.CERTIF,A.CERTIF_NO,A.REG_ADDR,TI.INS_NAME,T.REAL_NAME,M.REMARK , M.Operators ");
            sb.append("   from T_MERINFO_CHANGE       M,          \n");
	        sb.append("        TBL_MCHT_INF           A,          \n");
	        sb.append("        TBL_INS_INF            TI,         \n");
	        sb.append("        t_operators            T           \n");
	        sb.append("  where m.MID = A.MCHT_CD                  \n");
	        sb.append("    and A.BANK_BELONG_CD = TI.INS_ID_CD    \n");
	        sb.append("    and A.ext1 = to_char(T.ID)             \n");
	        if(Constance.isNotEmpty(minDate)&&Constance.isNotEmpty(maxDate)){ //审核时间
                sb.append("AND M.DT_AUDITOR_TIME >= TO_TIMESTAMP('" + minDate + "','YYYY-MM-DD') " );
                sb.append("AND M.DT_AUDITOR_TIME <= TO_TIMESTAMP('" + maxDate + "','YYYY-MM-DD') + 1 " );
            }
           //角色为代理商或商服宝会员或父级id为代理商            
 		   if(roleId.equals(Role.AGENT.getCode())||roleId.equals(Role.SFB_AGENT.getCode())){
	    	        sb.append("    and exists(select 1                                                        \n");
	    	        sb.append("           from tbl_mer_agent_relation ma                                      \n");
	    	        sb.append("          where ma.merno = a.mcht_cd                                           \n");
	    	        sb.append("            and ma.agentid =").append(opId).append(" ) \n");
 			}
 		   //登录人角色销售人员                   
 			if(roleId.equals(Role.MARKET_DIRECT.getCode())||roleId.equals(Role.MARKET_LEAD.getCode())){                                               
	    	        sb.append("    and exists (select 1                                                      \n");
	    	        sb.append("                   from t_oper_role orole           \n");
	    	        sb.append("                  where a.uc_bc_cd_area = orole.agentid                             \n");
	    	        sb.append("                    and orole.operid =").append(opId).append("  )   \n");
 			}
	        
		return sb.toString();
	}

	@Override
	protected String getOrderPartOfHQL() {
		return "ORDER BY M.DT_AUDITOR_TIME DESC";
	}

	public String getOrgCode() {
		return orgCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}

	public String getMerchantNo() {
		return merchantNo;
	}

	public void setMerchantNo(String merchantNo) {
		this.merchantNo = merchantNo;
	}

	public String getCardNo() {
		return cardNo;
	}

	public void setCardNo(String cardNo) {
		this.cardNo = cardNo;
	}

	public String getDealCode() {
		return dealCode;
	}

	public void setDealCode(String dealCode) {
		this.dealCode = dealCode;
	}

        public String getNameBusi() {
            return nameBusi;
        }
    
        public void setNameBusi(String nameBusi) {
            this.nameBusi = nameBusi;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }

        public String getRoleId() {
            return roleId;
        }

        public void setRoleId(String roleId) {
            this.roleId = roleId;
        }

        public String getOpId() {
            return opId;
        }

        public void setOpId(String opId) {
            this.opId = opId;
        }

        public String getStatus() {
            return status;
        }

        public void setStatus(String status) {
            this.status = status;
        }

        public String getMinDate() {
            return minDate;
        }

        public void setMinDate(String minDate) {
            this.minDate = minDate;
        }

        public String getMaxDate() {
            return maxDate;
        }

        public void setMaxDate(String maxDate) {
            this.maxDate = maxDate;
        }

		public String getOneOrgCode() {
			return oneOrgCode;
		}

		public void setOneOrgCode(String oneOrgCode) {
			this.oneOrgCode = oneOrgCode;
		}

		public String getGroupCd() {
			return groupCd;
		}

		public void setGroupCd(String groupCd) {
			this.groupCd = groupCd;
		}
        
		



	
}
